Imports Microsoft.VisualBasic
Imports System.Text
Imports SQLtoASPX.Support


Public Class DragDropListMasterVB

    Private TableGen As RawCreateScript
    Private mDatabaseName As String

    Public Sub New(ByVal TableGen As RawCreateScript)
        Me.TableGen = TableGen
    End Sub

    Public Function ToString(ByVal GroupTableName As String, ByVal GroupKeyColumnName As String, ByVal GroupDisplayColumnName As String, ByVal DestinationTableGroupID As String, ByVal DestinationTableName As String, ByVal DestinationTableID As String, ByVal TargetTableName As String, ByVal TargetTableDisplayColumn As String, ByVal TargetTableKeyColumn As String) As String
        Dim sb As New StringBuilder
        Dim spaces As New Space(0)

        sb.AppendLine("Imports Microsoft.VisualBasic")
        sb.AppendLine("Imports System.Data.SQLClient")
        sb.AppendLine("Imports System.Collections.Generic")
        sb.AppendLine("")
        sb.AppendLine("Partial Class " & DestinationTableName)
        spaces.Inc()
        sb.AppendLine(spaces.ToString & "Inherits System.Web.UI.Page")
        sb.AppendLine("")
        sb.AppendLine(spaces.ToString & "Protected UnassignedList As New HTMLUnorderedList")
        sb.AppendLine(spaces.ToString & "Protected AssignedList As New HTMLUnorderedList")
        sb.AppendLine(spaces.ToString & "Protected UnassignedListItems As String")
        sb.AppendLine(spaces.ToString & "Protected AssignedListItems As String")
        sb.AppendLine("")
        GetPageLoad(sb, TableGen, spaces)
        sb.AppendLine("")
        spaces.Inc()
        sb.Append(GetIsPostbackEqualFalse(spaces, TableGen))
        sb.Append(DeclareAndOpenConnection(spaces, TableGen))

        sb.AppendLine(spaces.ToString & "Dim SQL As String = ""select * from [" & GroupTableName & "]""")

        DimCommandAsNewSqlCommandPassSQLCommaConnection(sb, TableGen, spaces, "SQL")
        sb.AppendLine(spaces.ToString & "cbo" & DestinationTableName & ".DataValueField = """ & GroupKeyColumnName & """")
        sb.AppendLine(spaces.ToString & "cbo" & DestinationTableName & ".DataTextField = """ & GroupDisplayColumnName & """")
        sb.AppendLine(spaces.ToString & "cbo" & DestinationTableName & ".DataSource = Command.ExecuteReader")
        sb.AppendLine(spaces.ToString & "cbo" & DestinationTableName & ".DataBind()")
        sb.AppendLine(spaces.ToString & "cbo" & DestinationTableName & "_SelectedIndexChanged(Nothing, Nothing)")

        ConnectionDotClose(sb, TableGen, spaces)
        GetEndIf(sb, spaces, TableGen)
        spaces.Dec()
        sb.AppendLine(spaces.ToString & "End Sub")
        sb.AppendLine("")
        sb.AppendLine(spaces.ToString & "Protected Sub cmdSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSave.Click")
        sb.AppendLine("")
        sb.Append(DeclareAndOpenConnection(spaces, TableGen))
        sb.AppendLine("")
        spaces.Inc()
        sb.AppendLine(spaces.ToString & "Dim SQL as String = ""delete from [" & DestinationTableName & "] where  [" & DestinationTableName & "]." & DestinationTableGroupID & " = @" & DestinationTableGroupID & " """)
        DimCommandAsNewSqlCommandPassSQLCommaConnection(sb, TableGen, spaces, "SQL")

        sb.AppendLine(spaces.ToString & "command.Parameters.AddWithValue(""@" & DestinationTableGroupID & """, cbo" & DestinationTableName & ".SelectedValue)")
        CommandDotExecuteNonQuery(sb, TableGen, spaces)

        sb.AppendLine("")
        sb.AppendLine(spaces.ToString & "Dim WorkLocation() As String = Split(txtResult.Text, "","")")
        sb.AppendLine(spaces.ToString & "For Each x As String In WorkLocation")
        spaces.Inc()
        sb.AppendLine(spaces.ToString & "If x.Length > 0 Then")
        spaces.Inc()
        sb.AppendLine(spaces.ToString & "Dim InsertSQL as String = ""insert into [" & DestinationTableName & "] ( " & DestinationTableGroupID & ", " & DestinationTableID & " ) values ( @" & DestinationTableGroupID & ", @" & DestinationTableID & " )""")
        sb.AppendLine(spaces.ToString & "Command = New SqlCommand(InsertSQL, Connection)")
        sb.AppendLine(spaces.ToString & "Command.Parameters.AddWithValue(""@" & DestinationTableGroupID & """, cbo" & DestinationTableName & ".SelectedValue)")
        sb.AppendLine(spaces.ToString & "Command.Parameters.AddWithValue(""@" & DestinationTableID & """, Right(x, Len(x) - 4))")
        CommandDotExecuteNonQuery(sb, TableGen, spaces)
        spaces.Dec()

        GetEndIf(sb, spaces, TableGen)
        spaces.Dec()
        sb.AppendLine(spaces.ToString & "Next")
        ConnectionDotClose(sb, TableGen, spaces)
        'sb.AppendLine(spaces.ToString & "Response.Redirect(""Default.aspx"")")
        sb.AppendLine(spaces.ToString & "cbo" & DestinationTableName & "_SelectedIndexChanged(nothing,nothing)")
        spaces.Dec()
        sb.AppendLine(spaces.ToString & "End Sub")
        sb.AppendLine()
        sb.AppendLine("   Protected Sub cbo" & DestinationTableName & "_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbo" & DestinationTableName & ".SelectedIndexChanged")

        sb.AppendLine(spaces.ToString & "Dim SQL as String = ""SELECT [" & TargetTableName & "]." & TargetTableDisplayColumn & ", [" & TargetTableName & "]." & TargetTableKeyColumn & " FROM [" & TargetTableName & "] WHERE " & TargetTableKeyColumn & " not in ( select " & DestinationTableID & " from " & DestinationTableName & " where [" & DestinationTableName & "]." & DestinationTableGroupID & " = @" & DestinationTableGroupID & " ) ORDER BY [" & TargetTableName & "]." & TargetTableDisplayColumn & """")
        sb.Append(DeclareAndOpenConnection(spaces, TableGen))
        sb.AppendLine("")
        DimCommandAsNewSqlCommandPassSQLCommaConnection(sb, TableGen, spaces, "SQL")
        sb.AppendLine(spaces.ToString & "Command.Parameters.AddWithValue(""@" & DestinationTableGroupID & """, cbo" & DestinationTableName & ".SelectedValue)")
        sb.AppendLine("")
        DimReaderAsSQLDataReaderEqualsCommandDotExecuteReader(sb, TableGen, spaces)
        sb.AppendLine("")
        sb.AppendLine(spaces.ToString & "UnassignedList.ID = ""ul2""")
        sb.AppendLine(spaces.ToString & "UnassignedList.cssClass = ""draglist""")
        DoWhileReaderDotRead(sb, TableGen, spaces)
        spaces.Inc()
        sb.AppendLine(spaces.ToString & "UnassignedListItems = UnassignedListItems & ""new YAHOO.example.DDList(""""li2_"" & Reader(""" & TargetTableKeyColumn & """) & """""");"" & vbCrLf")
        sb.AppendLine(spaces.ToString & "UnassignedList.AddListItem(""li2_"" & Reader(""" & TargetTableKeyColumn & """), ""list2"", Reader(""" & TargetTableDisplayColumn & """))")
        spaces.Dec()

        VBLoop(sb, TableGen, spaces)
        ReaderDotClose(sb, TableGen, spaces)

        sb.AppendLine("")
        sb.AppendLine(spaces.ToString & " SQL = ""SELECT [" & TargetTableName & "]." & TargetTableDisplayColumn & ", [" & TargetTableName & "]." & TargetTableKeyColumn & " FROM [" & TargetTableName & "] inner join [" & DestinationTableName & "] on [" & TargetTableName & "]." & TargetTableKeyColumn & " = [" & DestinationTableName & "]." & DestinationTableID & " WHERE [" & DestinationTableName & "]." & DestinationTableGroupID & " = @" & DestinationTableGroupID & " ORDER BY [" & TargetTableName & "]." & TargetTableDisplayColumn & """")

        CommandEqualsNewSqlCommandPassSQLCommaConnection(sb, TableGen, spaces, "SQL")
        sb.AppendLine(spaces.ToString & "Command.Parameters.AddWithValue(""@" & DestinationTableGroupID & """, cbo" & DestinationTableName & ".SelectedValue)")
        sb.AppendLine(spaces.ToString & "Reader = Command.ExecuteReader")
        sb.AppendLine(spaces.ToString & "AssignedList.ID = ""ul1""")
        sb.AppendLine(spaces.ToString & "AssignedList.cssClass = ""draglist""")
        DoWhileReaderDotRead(sb, TableGen, spaces)
        spaces.Inc()
        sb.AppendLine(spaces.ToString & "AssignedListItems = AssignedListItems & ""new YAHOO.example.DDList(""""li1_"" & Reader(""" & TargetTableKeyColumn & """) & """""");"" & vbCrLf")
        sb.AppendLine(spaces.ToString & "AssignedList.AddListItem(""li1_"" & Reader(""" & TargetTableKeyColumn & """), ""list1"", Reader(""" & TargetTableDisplayColumn & """))")
        spaces.Dec()
        VBLoop(sb, TableGen, spaces)
        ReaderDotClose(sb, TableGen, spaces)

        ConnectionDotClose(sb, TableGen, spaces)
        sb.AppendLine("    End Sub")
        spaces.Dec()
        sb.AppendLine("End Class")


sb.appendline("")
sb.appendline("Public Class HTMLUnorderedList")
sb.appendline("        Private mArray As New List(Of String)")
sb.appendline("        Private mID As String")
sb.appendline("    Public Property ID() As String")
sb.appendline("        Get")
sb.appendline("            Return mID")
sb.appendline("        End Get")
sb.appendline("        Set(ByVal value As String)")
sb.appendline("            mID = value")
sb.appendline("        End Set")
sb.appendline("    End Property")
sb.appendline("")
sb.appendline("    Private mcssClass As String")
sb.appendline("    Public Property cssClass() As String")
sb.appendline("        Get")
sb.appendline("            Return mcssClass")
sb.appendline("        End Get")
sb.appendline("        Set(ByVal value As String)")
sb.appendline("            mcssClass = value")
sb.appendline("        End Set")
sb.appendline("    End Property")
sb.appendline("")
sb.appendline("    Public Overrides Function ToString() As String")
sb.appendline("        Dim sb As New StringBuilder")
sb.appendline("        sb.Append(""<ul"")")
sb.appendline("        If Mid.Length > 0 Then")
sb.appendline("            sb.Append("" id="""""" & mID & """""""")")
sb.appendline("        End If")
sb.appendline("")
sb.appendline("        If mcssClass.Length > 0 Then")
sb.appendline("            sb.Append("" class="""""" & mcssClass & """""""")")
sb.appendline("        End If")
sb.appendline("        sb.AppendLine("" > "")")
sb.appendline("")
sb.appendline("        For Each s As String In mArray")
sb.appendline("            sb.AppendLine(s)")
sb.appendline("        Next")
sb.appendline("")
sb.appendline("        sb.AppendLine(""</ul>"")")
sb.appendline("        Return sb.ToString()")
sb.appendline("    End Function")
sb.appendline("")
sb.appendline("    Public Sub AddListItem(ByVal id As String, ByVal cssClass As String, ByVal Text As String)")
sb.appendline("        mArray.Add(""    <li id="""""" & id & """""" class="""""" & cssClass & """""">"" & Text & ""</li>"")")
sb.appendline("    End Sub")
sb.appendline("")
sb.appendline("End Class")


        Return sb.ToString
    End Function
End Class
